package com.tro.common.util.mybatis;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;

import com.tro.common.util.string.StringUtlis;

import net.sf.json.JSONObject;
//区间 添加_BEGIN_LEEKO,_END_LEEKO
//等于 _EQUAL_LEEKO
//LIKERIGHT _LIKERIGHT_LEEKO
//LIKEALL _LIKEALL_LEEKO

public class SqlBuilder {
	@Deprecated
	public static String buildCondition(String key, String val, Integer jdbcType) {
		if (StringUtils.isBlank(val)) {
			return "";
		}
		StringBuilder sb = new StringBuilder();
		sb.append(" and ");
		key =StringEscapeUtils.escapeSql(key);
		Map<String, Object> condition = getVal(key, val, jdbcType);
		sb.append(condition.get("colName"));
		sb.append(condition.get("symbol"));
		sb.append(condition.get("value"));
		return sb.toString();
	}

	@Deprecated
	public static Map<String, Object> getVal(String key, String val, Integer kind) {

		Map<String, Object> result = new HashMap<String, Object>();

		result.put("colName", key);
		result.put("symbol", " = ");
		result.put("value", val);

		if (key.indexOf("_BEGIN_LEEKO") != -1) {
			result.put("colName", key.replace("_BEGIN_LEEKO", ""));
			result.put("symbol", " >= ");
			if (kind == 2) {
				result.put("value", "'" + val + "'");
			}
			if (kind == 3) {
				result.put("value", "to_date('" + val + "','yyyy-mm-dd')");
			}
			if (kind == 1) {
				result.put("value", val);
			}
			if (kind == 4) {
				result.put("value", "to_date('" + val + "','yyyy-mm-dd hh:mi:ss')");
			}

		}

		if (key.indexOf("_END_LEEKO") != -1) {
			result.put("colName", key.replace("_END_LEEKO", ""));
			result.put("symbol", " <= ");
			if (kind == 2) {
				result.put("value", "'" + val + "'");
			}
			if (kind == 3) {
				result.put("value", "to_date('" + val + "','yyyy-mm-dd')");
			}
			if (kind == 1) {
				result.put("value", val);
			}
			if (kind == 4) {
				result.put("value", "to_date('" + val + "','yyyy-mm-dd hh:mi:ss')");
			}
		}
		if (key.indexOf("_EQUAL_LEEKO") != -1) {
			result.put("colName", key.replace("_EQUAL_LEEKO", ""));
			result.put("symbol", " = ");
			if (kind == 2) {
				result.put("value", "'" + val + "'");
			}
			if (kind == 3) {
				result.put("value", "to_date('" + val + "','yyyy-mm-dd')");
			}
			if (kind == 1) {
				result.put("value", val);
			}
			if (kind == 4) {
				result.put("value", "to_date('" + val + "','yyyy-mm-dd hh:mi:ss')");
			}
		}
		// 当你看到这个的时候，其实我没考虑日期格式
		if (key.indexOf("_LIKERIGHT_LEEKO") != -1) {
			result.put("colName", key.replace("_LIKERIGHT_LEEKO", ""));
			result.put("symbol", " like ");
			result.put("value", "'" + val + "%'");
		}
		if (key.indexOf("_LIKEALL_LEEKO") != -1) {
			result.put("colName", key.replace("_LIKEALL_LEEKO", ""));
			result.put("symbol", " like ");
			result.put("value", "'%" + val + "%'");
		}

		return result;
	}
	public static String getRealKey(String key){
		key=key.replace("_LIKERIGHT_LEEKO", "");
		key=key.replace("_LIKEALL_LEEKO", "");
		key=key.replace("_EQUAL_LEEKO", "");
		key=key.replace("_END_LEEKO", "");
		key=key.replace("_BEGIN_LEEKO","");
		return key;
	}

	/**
	 * 
	 * @Title: builderCountSql
	 * @Description: TODO(这里用一句话描述这个方法的作用)
	 * @param @param
	 *            search
	 * @param @param
	 *            sqlStr
	 * @param @param
	 *            jdbcTypes
	 * @param @return
	 * @param @throws
	 *            Exception 设定文件
	 * @return String 返回类型
	 * @throws @author
	 *             leeko
	 */
	public static String builderCountSql(JSONObject search, String sqlStr, Map<String, Integer> jdbcTypes)
			throws Exception {
		StringBuilder selectSql = new StringBuilder();
		selectSql.append("select count(1) from ");
		selectSql.append("(");
		selectSql.append(sqlStr);
		if (sqlStr.toUpperCase().indexOf(" WHERE ") != -1) {

		} else {
			selectSql.append(" where 1=1 ");
		}
		if (!search.isNullObject() && !search.isEmpty()) {
			Iterator it = search.keys();
			while (it.hasNext()) {
				String key = (String) it.next();
				String searchVal = search.getString(key);

				if (StringUtils.isNotBlank(searchVal)) {
					
					selectSql.append(buildCondition(key, search.getString(key), jdbcTypes.get(getRealKey(key))));
				}

			}
		}
		selectSql.append(")");
		return selectSql.toString();
	}

	/**
	 * 
	 * @Title: buildSelectSql
	 * @Description: TODO(这里用一句话描述这个方法的作用)
	 * @param @param
	 *            search
	 * @param @param
	 *            isPage
	 * @param @param
	 *            pageSize
	 * @param @param
	 *            pageNum
	 * @param @param
	 *            jdbcTypes
	 * @param @param
	 *            sqlStr
	 * @param @return
	 * @param @throws
	 *            Exception 设定文件
	 * @return String 返回类型
	 * @throws @author
	 *             leeko
	 */
	public static String buildSelectSql(JSONObject search, boolean isPage, Integer pageSize, Integer pageNum,
			Map<String, Integer> jdbcTypes, String sqlStr) throws Exception {
		StringBuilder selectSql = new StringBuilder();
		if (isPage) {// 启用分页
			selectSql.append("select * from ( select row_.*, rownum rownum_ from ( ");
		}
		selectSql.append(sqlStr);
		if (sqlStr.toUpperCase().indexOf(" WHERE ") != -1) {

		} else {
			selectSql.append(" where 1=1 ");
		}
		if (!search.isNullObject() && !search.isEmpty()) {
			Iterator it = search.keys();
			while (it.hasNext()) {
				String key = (String) it.next();
				String searchVal = search.getString(key);
				if (StringUtils.isNotBlank(searchVal)) {
					
					selectSql.append(buildCondition(key, search.getString(key), jdbcTypes.get(getRealKey(key))));
				}

			}
		}

		if (isPage) {// 启用分页
			selectSql.append(") row_ ) where rownum_ > ");
			selectSql.append((pageSize - 1) * pageNum);

			selectSql.append(" and rownum_ <= ");
			selectSql.append(pageNum * pageSize);
		}
		return selectSql.toString();
	}
	
	
	/**
	 * 导出sql拼接
	 * */
	public static String buildSelectSqlNoPage(JSONObject search,String tableName,Map<String, Integer> jdbcTypes,String sqlValue){
		StringBuilder selectSql = new StringBuilder();
		if(StringUtils.isNotBlank(sqlValue)){
			selectSql.append(sqlValue);
		}else{
			selectSql.append("select * from "+tableName );
		}
		selectSql.append(" where 1=1 ");
		if (!search.isNullObject() && !search.isEmpty()) {
			Iterator it = search.keys();
			while (it.hasNext()) {
				String key = (String) it.next();
				String searchVal = search.getString(key);
				if (StringUtils.isNotBlank(searchVal)) {
					
					selectSql.append(buildCondition(key, search.getString(key), jdbcTypes.get(getRealKey(key))));
				}

			}
		}
		return selectSql.toString();
	}
	

	public static String buildDelSql(List<Object> ids, String tableName, Integer jdbcType) throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("delete ");
		sb.append(tableName);
		sb.append(" where ID in (");
		for (Object id : ids) {
			
			Condition condition = Condition.eq("ID", id, jdbcType);
			sb.append(condition.getValue());
			sb.append(",");
		}
		if (sb.toString().endsWith(",")) {
			sb.delete(sb.length() - 1, sb.length());
		}
		sb.append(")");
		return sb.toString();
	}

	public static String buildUpdateSql(Map<String, Object> map, String tableName, Map<String, Integer> jdbcTypes)
			throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("update ");
		sb.append(tableName);
		sb.append(" set ");
		Condition key = Condition.eq("ID", map.get("ID"), jdbcTypes.get("ID"));
		for (Map.Entry<String, Object> entry : map.entrySet()) {
			if(entry.getKey().equals("BASE_FORM_LEEKO_TID")){
				System.out.println("BASE_FORM_LEEKO_TID");
				continue;
			}
			Condition condition = Condition.eq(entry.getKey(), entry.getValue(), jdbcTypes.get(entry.getKey()));
			sb.append(condition.getSql());
			sb.append(",");
		}
		if (sb.toString().endsWith(",")) {
			sb.delete(sb.length() - 1, sb.length());
		}
		sb.append(" where ");
		sb.append(key.getSql());
		return sb.toString();
	}

	public static String buildInsertSql(Map<String, Object> map, String tableName, Map<String, Integer> jdbcTypes,
			Integer keyRule, Map<String, Object> defaultValues, String seqVal) throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into ");
		sb.append(tableName);
		sb.append("(");
		for (String key : map.keySet()) {
			if("BASE_FORM_LEEKO_TID".equals(key)){
				continue;
			}
			sb.append(key);
			sb.append(",");
		}
		if (sb.toString().endsWith(",")) {
			sb.delete(sb.length() - 1, sb.length());
		}
		sb.append(") values(");
		for (Map.Entry<String, Object> entry : map.entrySet()) {
			if(entry.getKey().equals("BASE_FORM_LEEKO_TID")){
				
				continue;
			}
			if (entry.getKey() .equals("ID")) {
				if (keyRule == 1) {
					sb.append("'");
					sb.append(StringUtlis.getGUID());
					sb.append("',");
				} else {
					if (StringUtils.isNotBlank(seqVal)) {
						sb.append(seqVal);
						sb.append(".Nextval");
						sb.append(",");
						continue;
					} else {
						throw new Exception("参数有误！");
					}
				}
			}
			Condition condition = Condition.eq(entry.getKey(), entry.getValue(), jdbcTypes.get(entry.getKey()));
			sb.append(condition.getValue());
			sb.append(",");
		}
		if (sb.toString().endsWith(",")) {
			sb.delete(sb.length() - 1, sb.length());
		}
		sb.append(") ");
		return sb.toString();
	}

	public static void main(String[] args) {
		List<String> list = new ArrayList<>();
		for (int i = 0; i < 10; i++) {
			list.add("'" + i + "'");
		}
		System.out.println(list.toString());
	}
}
